#! /bin/bash
PRESTO_HOME=/opt/cloudera/parcels/presto/bin/presto
if [[ $1 == '' ]];
then dt=`date -d '-1 day' "+%Y-%m-%d"`
else dt=$1
fi


/export/server/presto/bin/presto --catalog hive --server hadoop01:8090 --execute"
insert into hive.zx_dwb.dwb_intention_detail
select dfcr.id,
       dfcr.create_date_time,
       dfcr.deleted,
       dfcr.customer_id,
       dfcr.origin_type,
       dfcr.itcast_subject_id,
       dfcr.itcast_school_id,
       dfcr.origin_channel,
       dfcr.creator,
        -- 从校区信息表关联到的字段
       ddis.name as school_name,
        -- 从学科信息表关联到的字段
       dis.name as subject_name,
        -- 从员工表关联到的字段
       cast (dde.department_id as integer) as tdepart_id,
        -- 从部门表关联到的字段
       dsd.name as dept_name,
        -- 从客户静态信息表关联到的字段
       ddc.area,
        -- 从客户线索表关联到的字段
       ddcc.id as clus_id,
       ddcc.clue_state,
       -- 从线索申诉表关联到的字段
       cast (ddca.appeal_status as varchar) as appeal_status,
       dfcr.start_date as dt
from (select * from hive.zx_dwd.dwd_fact_customer_relationship where updata_time = '9999-99-99') dfcr
left join hive.zx_dwd.dwd_dim_customer ddc --客户静态信息表
    on dfcr.customer_id = ddc.id and ddc.updata_time = '9999-99-99'
left join hive.zx_dwd.dwd_dim_itcast_school ddis -- 校区信息表
    on dfcr.itcast_school_id = ddis.id
left join hive.zx_dwd.dwd_dim_itcast_subject dis -- 学科信息表
    on dfcr.itcast_subject_id = dis.id
left join hive.zx_dwd.dwd_dim_customer_clue ddcc -- 客户线索表
    on dfcr.id = ddcc.customer_relationship_id and ddcc.updata_time = '9999-99-99'
left join hive.zx_dwd.dwd_dim_customer_appeal ddca -- 线索申诉表
    on ddcc.customer_relationship_id = ddca.customer_relationship_first_id and ddca.updata_time = '9999-99-99'
left join hive.zx_dwd.dwd_dim_employee dde -- 员工信息表
    on dfcr.creator = dde.id
left join hive.zx_dwd.dwd_dim_scrm_department dsd -- 员工部门表
    on cast (dde.department_id as integer) = cast (dsd.id as integer);
"


